The series so far:

1. Introduction to SQL Server Security — Part 1
2. Introduction to SQL Server Security — Part 2
3. Introduction to SQL Server Security — Part 3
4. Introduction to SQL Server Security — Part 4
5. Introduction to SQL Server Security — Part 5
6. Introduction to SQL Server Security — Part 6
7.  

SQL Server provides a number of tools for helping you ensure that your environment and data remain secure. Some of the most important of these come in the form of catalog views, dynamic management views (DMVs), and built-in system functions. By incorporating them into your T-SQL statements, you can retrieve important security-related information about your SQL Server environment, such as which principals are enabled, what permissions are assigned to those principals, or how auditing is implemented on database objects.

In this article, I cover a number of these views and functions and provide examples of how you can use them to retrieve details about your system’s security. I created the statements in SQL Server Management Studio (SSMS), using SQL Server 2017, although most of the information I cover can apply to SQL Server editions going back to 2014 or earlier.

Microsoft divides security-related catalog views into server-level views, database-level views, encryption-related views, and auditing-related views. I’ve taken the same approach here, providing a section for each type. Because there are fewer security-related DMVs and system functions, I’ve provided only a single section for each of them.

In all cases, I cover only a portion of the available views and functions, but enough to give you a sense of how you can use them when administering a SQL Server instance. Just be aware that Microsoft provides plenty of others, so be sure to refer to SQL Server documentation as necessary to learn about those views and functions I haven’t covered here.

Server-Level Catalog Views

SQL Server offers numerous security-related catalog views that operate at the server level. Two of the most useful are sys.server_principals and sys.server_permissions. The sys.server_principals view returns a row for each existing server principal, and the sys.server_permissions view returns a row for each assigned server permission.

You can join these views together to see the permissions granted on specific principals. For example, the following SELECT statement returns the permissions assigned to SQL logins that have been enabled:

The sys.server_principals view includes the type_desc column, which indicates the principal type. SQL Server supports six types of server principals, including SQL_LOGIN, WINDOWS_LOGIN and SERVER_ROLE. The view also returns the is_disabled column, which indicates whether a principal is enabled (0) or disabled (1). Both columns are specified in the WHERE clause to filter out all but enabled SQL logins. Figure 1 shows the results returned by the SELECT statement on my system, which includes only a few test logins.

Figure 1. Permissions granted on SQL logins

You can also use the sys.server_principals view in conjunction with the sys.server_role_members view, which returns a row for each member of a fixed or user-defined server role. Because the sys.server_principals view includes all principals, including roles and logins, you can join the view to itself and to the sys.server_role_members view to retrieve the members of a specific role, as shown in the following example:

The statement limits the results to the sysadmin role and to SQL logins. Self-joining the sys.server_principals view makes it possible to retrieve both the role and login names. Figure 2 shows the results I received on my system, which indicate that the only two SQL logins are members of the sysadmin role, one of which is the sa account.

Figure 2. SQL logins in the sysadmin role

Another interesting catalog view is sys.system_components_surface_area_configuration, which returns a row for each executable object that can be enabled or disabled through SQL Server’s surface area configuration features. For example, the following statement uses the view to return a list of stored procedures included in the SMO and DMO XPs component:

The type_desc column in the WHERE clause specifies the object type (SQL_STORED_PROCEDURE), and the state column indicates that the object should be enabled (1), rather than disabled (0). Figure 3 shows the results returned on my system.

Figure 3. Stored procedures in the surface area configuration

The database_name value indicates which database contains the object. The possible values include master, msdb, and mssqlsystemresource (a read-only database that contains all the system objects).

Database-Level Catalog Views

Many of the database-level catalog views work much like their server-level counterparts, except that they’re specific to the current database. To try out some of these views, first create the Test1 database and then add the Sales schema (which will be used in later examples):

After you create the database, you can run the catalog views within the context of that database. For example, you can use the sys.database_principals view to retrieve details about the existing database principals, and you can use the sys.database_permissions view to retrieve details about assigned database permissions. Similar to what you saw at the server level, you can join these views to see the permissions assigned to specific database principals. For example, the following SELECT statement returns certain types of permissions granted to the public role:

The statement limits the results to permissions in the DATABASE class. However, the view can also return permissions for such classes as OBJECT_OR_COLUMN, SCHEMA, or DATABASE_PRINCIPAL, depending on what permissions have been assigned at the database level. Figure 4 shows the results that the SELECT statement returned on my system.

Figure 4. Database permissions granted to the public role

One way to check effective permissions without writing scripts yourself is to use Redgate’s SQL Census. It creates a report of who has access to what on your SQL Servers and makes best practice recommendations, like disabling SA accounts. It’s still in development but it’s a good starting point to check on your SQL Server permissions and undertake any necessary cleaning tasks.

SQL Server also provides database-level catalog views that do not have a server counterpart (and vice versa). For example, you can use the sys.master_key_passwords view to retrieve information about the database master key password, if the password was added by using the sp_control_dbmasterkey_password stored procedure.

To see how this works, start by creating a database master key in the Test1 database, as shown in the following example (using a stronger password than the one I’ve included here):

After you’ve created the master key, use the sp_control_dbmasterkey_password stored procedure to add a credential that specifies the same password as the one used when creating the database master key:

When SQL Server tries to decrypt the database master key, it first attempts to use the service master key. If this doesn’t work, SQL Server searches the credential store for a master key credential, using that if it exists.

After you’ve created the master key credential, you can use the sys.master_key_passwords view to retrieve information about that credential:

The statement returns only the credential_id and family_guid values, as shown in Figure 5. The family_guid column displays the unique ID assigned to the database when it was originally created.

Figure 5. Configured database master key password

You can also use the sys.credentials view to retrieve credential information, as shown in the following example:

Although the sys.credentials view is a server-level catalog view, you can use it to see credentials created for database master keys. On my system, the SELECT statement returns the results shown in Figure 6.

Figure 6. Credential associated with the Test1 database master key

The results indicate that I have only one credential created on my SQL Server instance, the one for the Test1 database master key. Notice that the credential_id value shown here is the same value shown in Figure 5.

SQL Server also provides the sys.database_scoped_credentials view, which returns a row for each database-scoped credential in the database. The following SELECT statement uses the view within the context of the Test1 database:

The SELECT statement returns no rows for Test1. This is because the credential created for the database master key exists at the server level, not the database level.

Encryption-Related Catalog Views

The security-related catalog views also include about a dozen specific to SQL Server’s encryption features. One of these views is sys.certificates, which you can use to retrieve details about the certificates that exist in a database. To see the view in action, first create a certificate named Cert1 in the Test1 database, using the subject customer credit cards (or whatever subject you want to use):

When you create a certificate without specifying a password, SQL Server uses the database master key to encrypt the certificate, which means that a master key must already exist. (You created the key in the previous section.) You can now use the sys.certificates view to retrieve information about the certificate:

Figure 7 shows the results returned on my system. Notice that the encryption type is ENCRYPTED_BY_MASTER_KEY.

Figure 7. Cert1 certificate in the Test1 database

SQL Server also lets you add asymmetric and symmetric keys to your database. If you’ve added either type of key, you can use the sys.asymmetric_keys or sys.symmetric_keys view to return details about them. For example, suppose you create the following asymmetric key in the Test1 database:

The statement adds an asymmetric key named Akey1, using RSA encryption and password protection. You can now use the sys.asymmetric_keys view to retrieve information about the new key:

Figure 8 shows the results returned by the SELECT statement.

Figure 8. Akey1 asymmetric key in the Test1 database

The SELECT statement returns the key name, encryption type, algorithm, and key length, all of which were specified when creating the asymmetric key.

Auditing-Related Catalog Views

The final category of security-related catalog views includes those specific to SQL Server’s auditing features. If you’ve implemented auditing, these views can be particularly handy, especially the sys.server_audits and sys.database_audit_specifications views. The sys.server_audits view returns information about server audit objects, and the sys.database_audit_specifications view returns information about database audit specifications.

To see both views in action, start by creating and enabling the SrvAudit1 audit and the DbSpec1 database specification in the Test1 database:

After you’ve created your server audit object, you can use the sys.server_audits view to view that object, specifying the audit name in your WHERE clause, as shown in the following example:

The statement returns the results shown in Figure 9. Notice that the type_desc value is FILE, indicating that the audit log is saved to the file system rather than to the Security or Application log. The figure also indicates that the audit is enabled. (The is_enabled value is 1.)

Figure 9. SrvAudit1 audit created on the server

You can then use the sys.database_audit_specifications view to view information about the database audit specification:

The statement returns the results shown in Figure 10.

Figure 10. DbSpec1 database audit specification

SQL Server also provides several other catalog views specific to auditing, but their use depends on how you’ve configured auditing on your SQL Server instance. For example, if you create server audit specifications, you can use the sys.server_audit_specifications view to retrieve information about those specifications.

Dynamic Management Views

As with catalog views, SQL Server offers DMVs specific to auditing. One of these views is sys.dm_audit_actions, which lets you retrieve event-related information about audit actions and audit groups. For example, the following SELECT statement uses the view to return the IDs and names of the actions or groups with a class_desc value of LOGIN and a covering_parent_action_name value of LOGIN_CHANGE_PASSWORD_GROUP:

The class_desc column refers to the object class that the audit action applies to. The covering_parent_action_name column is the audit action or group that contains the row’s audit action. On my system, the SELECT statement returned the results shown in Figure 11.

Figure 11. Audit actions in the audit log

Another DMV specific to auditing is sys.dm_server_audit_status view, which returns information about server audit objects. In the following example, the SELECT statement uses the view to retrieve the ID, name, status, and file size of each defined audit:

The only audit I had defined on my system when I ran this statement was the one created in the previous section, giving me the results shown in Figure 12.

Figure 12. SrvAudit1 audit status

Security-related DMVs are not limited to auditing. SQL Server also provides several views specific to encryption, such as the sys.dm_database_encryption_keys view, which returns details about a database’s encryption state and its encryption keys.

You can see how the view works by setting up Transparent Data Encryption (TDE) on the Test1 database. For this, you need to take the following steps:

1. Create a database master key in the master database, if the key doesn’t already exist.
2. Create a certificate in the master database for securing the master key.
3. Create a database encryption key in the Test1 database.

Normally, there would be an additional step to enable TDE on the Test1 database, but that’s not necessary to demonstrate how the sys.dm_database_encryption_keys view works.

To create a database master key in the master database, run the following CREATE MASTER KEY statement, providing a much more robust password, of course:

You can then use the sys.symmetric_keys catalog view to view information about the master database key:

Figure 13 shows the results returned by the SELECT statement on my system. When you create a database master key in the master database, SQL Server also adds a service master key.

Figure 13. Database and service master keys

The next step is to create a certificate in the master database for securing the master key. You can do this easily enough by running the following CREATE CERTIFICATE statement:

To verify that the certificate has been created, you can use the sys.certificates view, as shown in the following example:

Because the master database on my system includes only one certificate, the SELECT statement returns only one row, which is shown in Figure 14.

Figure 14. TdeCert certificate in the master database

The final step is to create a database encryption key in the Test1 statement. For this, you can use the following CREATE DATABASE ENCRYPTION KEY statement, specifying the TdeCert certificate you created in the master database:

When you run this statement, you should receive the following warning about backing up the certificate:

This completes the steps you need to take to prepare your database to try out the sys.dm_database_encryption_keys view. The following SELECT statement uses the view to retrieve the encryption state, key algorithm, key length and encryption type of the database encryption key:

If you’ve been following along, the statement should return the results shown in Figure 15.

Figure 15. Database encryption key for the Test1 database

I realize that this is a long way to go about testing the sys.dm_database_encryption_keys view, but SQL Server supports only a handful of security-related DMVs, and I wanted to be sure you got to see some of them in action. That said, the TDE example I used here is actually based on one I created for the Simple Talk article Encrypting SQL Server: Transparent Data Encryption (TDE), which provides more specific information about enabling TDE on a SQL Server database.

Security-Related Functions

In addition to the catalog views and DMVs, SQL Server provides a number of security-related system functions. For example, you can use the sys.fn_builtin_permissions table-valued function to return details about the server’s built-in permission hierarchy or a subset of that hierarchy, as shown in the following SELECT statement:

In this case, the sys.fn_builtin_permissions function takes DEFAULT as an argument, which means the function will return a complete list of built-in permissions. However, the WHERE clause limits those results to the DELETE permission. Figure 16 shows the results that were returned on my system.

Figure 16. Built-in DELETE permissions on the SQL Server instance

SQL Server also provides a number of system functions for working with user and login accounts. To demonstrate how some of these work, first create the sqllogin1 login and then create the sqluser1 user in the Test1 database, based on the sqllogin1 login:

The script also includes a GRANT statement that assigns the SELECT, INSERT, and DELETE permissions to sqluser1 on the Sales schema. You can verify these permissions by running the fn_my_permissions table-valued function within the security context of sqluser1:

The EXECUTE AS USER statement changes the security context to sqluser1, and the REVERT statement changes the security context back to the original user. Because the SELECT statement runs under the context of sqluser1, it returns the results shown in Figure 17, which verify the permissions assigned to that user.

Figure 17. Permissions granted to sqluser1

SQL Server also provides a number of scaler functions for verifying a user’s identity, including the following:

• The SUSER_NAME function returns the user’s login identification name.
• The SUSER_ID function returns the user’s login identification number.
• The SUSER_SID function returns the user’s login security identification number (SID).
• The USER_NAME function returns the user’s database user account name.
• The USER_ID function returns the user’s database user identification number.

For each of these functions, you can provide a parameter value or you can provide no value, in which case the function uses the current user or login account. You can test this out by calling the functions within the security context of sqluser1:

Figure 18 shows the results I received on my system. Notice that the results list the correct user name and the login name associated with that user.

Figure 18. User and login names and IDs for sqluser1

SQL Server also provides the IS_MEMBER scalar function for verifying whether the current user is the member of a specified group or role. For example, the following SELECT statement uses the function to determine whether sqluser1 is a member of the db_owner role:

The IS_MEMBER function can return only one of three values:

• If 0 is returned, the user is not a member of the specified group or role.
• If 1 is returned, the user is a member of the specified group or role.
• If NULL is returned, the group or role is not valid.

Figure 19 shows the results returned by the SELECT statement. As expected, the user is not a member of the db_owner role (unless you added the user to the role).

Figure 19. Verifying sqluser1 membership

Another fun built-in function is PWDCOMPARE, which lets you compare an existing password to a specified password. In this way, you can test for blank passwords or inadequate or common passwords, such as pa$$word.

To try out the function, first create the sqllogin2 login with a blank password (not in a production environment):

Next, run the following SELECT statement, using the PWDCOMPARE function in the WHERE clause to return any logins with a blank password:

The first argument passed into the PWDCOMPARE function is the unencrypted password, which in this case is an empty string. The second argument tells the function to use the password encryption hash. The function returns a 1 if the specified password matches the user’s actual password. Otherwise, the function returns 0. In this case, the WHERE clause specifies that the function must return 1 for the row to be returned. Figure 20 shows the results I received on my system.

Figure 20. Comparing login passwords

The SELECT statement should return only the sqllogin2 login. If your results include other logins, you might want to reevaluate your current security strategy.

SQL Server Security Views and Functions

SQL Server provides a number of security-related catalog views, DMVs, and system functions in addition to what I covered here, and you certainly should take the time to learn about what’s out there. These views and functions can be very useful when trying to understand and troubleshoot security on a SQL Server instance. The more familiar you are with what’s available, the easier it will be for you to do your job and the more effectively you can ensure the security of your data.